﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using OpenSISPortalNotesBusinessLayer;
using OpenSISDataConnection;
using System.Globalization;
using System.Resources;


namespace OpenSISPortalNotesDataLayer
{
    /// <summary>
    /// Summary description for PortalNotesDL
    /// </summary>
    /// 

    public class PortalNotesDL
    {
        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];
        //public static string server = "MSSQLServer";

        public System.Data.DataTable Checksortorder(PortalNotesBL portalnotes)
        {


            DataTable dtsortorder = null;
            dtsortorder = DataConnection.ConnectionPath.Getdata("select short_order from Portal_Notes where sch_id = " + portalnotes.SchoolID + " and short_order = " + portalnotes.Sortorder + " and visible_start_date <= '" + DateTime.Today + "'and visible_end_date >='" + DateTime.Today + "'").Tables[0];
            return dtsortorder;

        }
        public void PortalNotesAdd(PortalNotesBL portalnotes)
        {

            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                DataSet dsShortOrder = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax portal_note_id,Portal_Notes");

                dsShortOrder = DataConnection.ConnectionPath.Getdata("select isnull(max(short_order),0) + 1 as short_order from Portal_Notes where sch_id='" + portalnotes.SchoolID + "'");

                String strPortalNotesAdd = "INSERT into Portal_Notes(portal_note_id,sch_id,title,note,short_order,visible_start_date,visible_end_date,visible_staff_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ", N'" + portalnotes.SchoolID + "', N'" + portalnotes.Title.Replace("'", "''") + "', N'" + portalnotes.Notes.Replace("'", "''") + "', N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "', N'" + portalnotes.Visiblestartdate + "',N'" + portalnotes.Visibleenddate + "',N'" + portalnotes.Visiblestaffid + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strPortalNotesAdd);


            }

            if (server == "MySQL")
            {

                DateTime s = Convert.ToDateTime(portalnotes.Visiblestartdate, DateTimeFormatInfo.InvariantInfo);
                portalnotes.Visiblestartdate = s.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(portalnotes.Visibleenddate, DateTimeFormatInfo.InvariantInfo);
                portalnotes.Visibleenddate = d.ToString("yyyy/MM/dd");
               

                DataSet ds = null;

                DataSet dsShortOrder = null;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('portal_note_id','Portal_Notes')");

                dsShortOrder = DataConnection.ConnectionPath.Getdata("select IFNULL(max(short_order),0) + 1 as short_order from Portal_Notes where sch_id='" + portalnotes.SchoolID + "'");

                String strPortalNotesAdd = "INSERT into Portal_Notes(portal_note_id,sch_id,title,note,short_order,visible_start_date,visible_end_date,visible_staff_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ", N'" + portalnotes.SchoolID + "', N'" + portalnotes.Title.Replace("'", "''") + "', N'" + portalnotes.Notes.Replace("'", "''") + "', N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "', N'" + portalnotes.Visiblestartdate + "',N'" + portalnotes.Visibleenddate + "',N'" + portalnotes.Visiblestaffid + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strPortalNotesAdd);
            }
            else
            {
            }

           
        }

        //commented on 3rd aug 09
        //public DataSet Populatestafftypecheckbox()
        //{
        //    DataSet ds = null;
        //    ds = DataConnection.ConnectionPath.Getdata("select * from staff_type ");
        //    return ds;
        //}

        public DataSet Populatestafftypecheckbox()
        {
            DataSet ds = null;
            //ds = DataConnection.ConnectionPath.Getdata("select * from staff_type where Language_Type = '" + System.Web.HttpContext.Current.Session["MLCulture"].ToString() + "'");
            ds = DataConnection.ConnectionPath.Getdata("select * from staff_type where Language_Type = 'en-US'");

            return ds;
        }

        //commented on 3rd aug 09
        //public DataSet PopulateSelectedstafftypecheckbox(PortalNotesBL portalnotes)
        //{
        //    DataSet ds = null;
        //    ds = DataConnection.ConnectionPath.Getdata("select * from staff_type where stf_type_id in(" + portalnotes.Visiblestaffid + ")");
        //    return ds;
        //}


        public DataSet PopulateSelectedstafftypecheckbox(PortalNotesBL portalnotes)
        {
            DataSet ds = null;
         //   ds = DataConnection.ConnectionPath.Getdata("select * from staff_type where stf_type_cd in(" + portalnotes.Visiblestaffid + ") and Language_Type = '" + System.Web.HttpContext.Current.Session["MLCulture"].ToString() + "'");
            ds = DataConnection.ConnectionPath.Getdata("select * from staff_type where stf_type_cd in(" + portalnotes.Visiblestaffid + ") and Language_Type = 'en-US'");
            return ds;
        }

        public System.Data.DataTable PortalNotes(PortalNotesBL portalnotes)
        {



            if (server == "MSSQLServer")
                {
                    DataTable dt = null;
                    dt = DataConnection.ConnectionPath.Getdata("select * from Portal_Notes where sch_id = " + portalnotes.SchoolID + " and  visible_start_date <= '" + DateTime.Today.ToString(DateTimeFormatInfo.InvariantInfo) + "'and visible_end_date >='" + DateTime.Today.ToString(DateTimeFormatInfo.InvariantInfo) + "'  ORDER BY(short_order)").Tables[0];
                    return dt;
                }
                if (server == "MySQL")
                {
                    DataTable dt = null;
                    dt = DataConnection.ConnectionPath.Getdata("select * from Portal_Notes where sch_id = " + portalnotes.SchoolID + " and  visible_start_date <= '" + DateTime.Today.ToString("yyyy/MM/dd", DateTimeFormatInfo.InvariantInfo) + "'and visible_end_date >='" + DateTime.Today.ToString("yyyy/MM/dd", DateTimeFormatInfo.InvariantInfo) + "'  ORDER BY(short_order)").Tables[0];
                    return dt;
                }
                else
                {

                    DataTable dt = null;
                    dt = DataConnection.ConnectionPath.Getdata("select * from Portal_Notes where sch_id = " + portalnotes.SchoolID + " and  visible_start_date <= '" + DateTime.Today.ToString(DateTimeFormatInfo.InvariantInfo) + "'and visible_end_date >='" + DateTime.Today.ToString(DateTimeFormatInfo.InvariantInfo) + "'  ORDER BY(short_order)").Tables[0];
                    return dt;

                
                }
          
        }


        public void DeletePortalNote(PortalNotesBL Portalnote)
        {

            string strPortalNotesDelete = "delete from portal_notes where portal_note_id=" + Portalnote.PortalNotesid + "";

            DataConnection.ConnectionPath.ExecuteCommand(strPortalNotesDelete);

        }

        public void UpdatePortalNotes(PortalNotesBL PortalNotes)
        {
            if (server == "MSSQLServer")
            {
                string strUpdateGradeLevel = "update Portal_Notes set title= N'" + PortalNotes.Title.Replace("'", "''") + "',note= N'" + PortalNotes.Notes.Replace("'", "''") + "',visible_start_date= N'" + PortalNotes.Visiblestartdate + "',visible_end_date= N'" + PortalNotes.Visibleenddate + "',visible_staff_id= N'" + PortalNotes.Visiblestaffid + "' where portal_note_id=" + PortalNotes.PortalNotesid + "";

                DataConnection.ConnectionPath.ExecuteCommand(strUpdateGradeLevel);
            
            }
            if (server == "MySQL")
            {
                DateTime s = Convert.ToDateTime(PortalNotes.Visiblestartdate,DateTimeFormatInfo.InvariantInfo);
                PortalNotes.Visiblestartdate = s.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(PortalNotes.Visibleenddate, DateTimeFormatInfo.InvariantInfo);
                PortalNotes.Visibleenddate = d.ToString("yyyy/MM/dd");

                string strUpdateGradeLevel = "update Portal_Notes set title= N'" + PortalNotes.Title.Replace("'", "''") + "',note= N'" + PortalNotes.Notes.Replace("'", "''") + "',visible_start_date= N'" + PortalNotes.Visiblestartdate + "',visible_end_date= N'" + PortalNotes.Visibleenddate + "',visible_staff_id= N'" + PortalNotes.Visiblestaffid + "' where portal_note_id=" + PortalNotes.PortalNotesid + "";

                DataConnection.ConnectionPath.ExecuteCommand(strUpdateGradeLevel);
            }

           



           
        }

        public void UpdatePortalNotesShortOrder(PortalNotesBL PortalNotes)
        {

            string strUpdateNotesOrder = "update Portal_Notes set short_order= N'" + PortalNotes.Sortorder.Replace("'", "''") + "' where portal_note_id=" + PortalNotes.PortalNotesid + "";

            DataConnection.ConnectionPath.ExecuteCommand(strUpdateNotesOrder);

        }

        //public System.Data.DataTable PortalNoteDetailsByID(PortalNotesBL PortalNotes)
        //{
        //    DataTable dt = null;
        //    dt = DataConnection.ConnectionPath.Getdata("select * from Portal_Notes where portal_note_id='" + PortalNotes.PortalNotesid + "'").Tables[0];
        //    return dt;
        //}

    }
}
